const express = require('express');
const router = express.Router();
const { pool } = require('../db');

// 获取所有样品
router.get('/', async (req, res) => {
  try {
    const { keyword } = req.query;
    let sql = 'SELECT id, year, DATE_FORMAT(date, "%Y-%m-%d") as date, sample_code, sample_name, content, created_at, updated_at FROM samples ORDER BY created_at DESC';
    let params = [];
    
    if (keyword) {
      sql = 'SELECT id, year, DATE_FORMAT(date, "%Y-%m-%d") as date, sample_code, sample_name, content, created_at, updated_at FROM samples WHERE sample_name LIKE ? OR sample_code LIKE ? OR year LIKE ? ORDER BY created_at DESC';
      const searchTerm = `%${keyword}%`;
      params = [searchTerm, searchTerm, searchTerm];
    }
    
    const [rows] = await pool.execute(sql, params);
    
    res.json({
      success: true,
      data: rows,
      total: rows.length
    });
  } catch (error) {
    console.error('获取样品列表失败:', error);
    res.status(500).json({
      success: false,
      error: '获取样品列表失败'
    });
  }
});

// 根据 ID 获取样品
router.get('/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);
    const [rows] = await pool.execute(
      'SELECT id, year, DATE_FORMAT(date, "%Y-%m-%d") as date, sample_code, sample_name, content, created_at, updated_at FROM samples WHERE id = ?',
      [id]
    );
    
    if (rows.length === 0) {
      return res.status(404).json({
        success: false,
        error: '样品不存在'
      });
    }
    
    res.json({
      success: true,
      data: rows[0]
    });
  } catch (error) {
    console.error('获取样品信息失败:', error);
    res.status(500).json({
      success: false,
      error: '获取样品信息失败'
    });
  }
});

// 添加新样品
router.post('/', async (req, res) => {
  try {
    const { year, date, sample_code, sample_name, content } = req.body;
    
    if (!year || !date || !sample_code || !sample_name || !content) {
      return res.status(400).json({
        success: false,
        error: '缺少必要字段'
      });
    }
    
    // 检查样品编号是否已存在
    const [existingSamples] = await pool.execute(
      'SELECT id FROM samples WHERE sample_code = ?',
      [sample_code]
    );
    
    if (existingSamples.length > 0) {
      return res.status(400).json({
        success: false,
        error: '样品编号已存在'
      });
    }
    
    // 插入新样品
    const [result] = await pool.execute(
      'INSERT INTO samples (year, date, sample_code, sample_name, content) VALUES (?, ?, ?, ?, ?)',
      [year, date, sample_code, sample_name, JSON.stringify(content)]
    );
    
    const newSampleId = result.insertId;
    
    // 获取新创建的样品信息
    const [newSample] = await pool.execute(
      'SELECT id, year, DATE_FORMAT(date, "%Y-%m-%d") as date, sample_code, sample_name, content, created_at, updated_at FROM samples WHERE id = ?',
      [newSampleId]
    );
    
    res.status(201).json({
      success: true,
      data: newSample[0],
      message: '样品添加成功'
    });
  } catch (error) {
    console.error('添加样品失败:', error);
    res.status(500).json({
      success: false,
      error: '添加样品失败'
    });
  }
});

// 更新样品信息
router.put('/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);
    const { year, date, sample_code, sample_name, content } = req.body;
    
    // 检查样品是否存在
    const [existingSample] = await pool.execute(
      'SELECT id FROM samples WHERE id = ?',
      [id]
    );
    
    if (existingSample.length === 0) {
      return res.status(404).json({
        success: false,
        error: '样品不存在'
      });
    }
    
    // 检查样品编号是否被其他样品使用
    if (sample_code) {
      const [duplicateCheck] = await pool.execute(
        'SELECT id FROM samples WHERE sample_code = ? AND id != ?',
        [sample_code, id]
      );
      
      if (duplicateCheck.length > 0) {
        return res.status(400).json({
          success: false,
          error: '样品编号已被其他样品使用'
        });
      }
    }
    
    // 更新样品信息
    await pool.execute(
      'UPDATE samples SET year = ?, date = ?, sample_code = ?, sample_name = ?, content = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
      [year, date, sample_code, sample_name, JSON.stringify(content), id]
    );
    
    // 获取更新后的样品信息
    const [updatedSample] = await pool.execute(
      'SELECT id, year, DATE_FORMAT(date, "%Y-%m-%d") as date, sample_code, sample_name, content, created_at, updated_at FROM samples WHERE id = ?',
      [id]
    );
    
    res.json({
      success: true,
      data: updatedSample[0],
      message: '样品信息更新成功'
    });
  } catch (error) {
    console.error('更新样品信息失败:', error);
    res.status(500).json({
      success: false,
      error: '更新样品信息失败'
    });
  }
});

// 删除样品
router.delete('/:id', async (req, res) => {
  try {
    const id = parseInt(req.params.id);
    
    // 检查样品是否存在
    const [existingSample] = await pool.execute(
      'SELECT id, sample_name FROM samples WHERE id = ?',
      [id]
    );
    
    if (existingSample.length === 0) {
      return res.status(404).json({
        success: false,
        error: '样品不存在'
      });
    }
    
    // 删除样品
    await pool.execute('DELETE FROM samples WHERE id = ?', [id]);
    
    res.json({
      success: true,
      data: existingSample[0],
      message: '样品删除成功'
    });
  } catch (error) {
    console.error('删除样品失败:', error);
    res.status(500).json({
      success: false,
      error: '删除样品失败'
    });
  }
});

module.exports = router; 